package com.softer.chengxin.scheduled.dao;

import com.softer.chengxin.score.entity.ScoreRecord;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author Gong Zhiyang
 * @date 2018/10/17
 */
@Mapper
@Repository
public interface ScheduledTaskDao {
    /**
     * 定时查询失效时间，过期将状态改为失效
     */
    @Update("UPDATE score_detail SET state =8 WHERE effective_end_time<NOW()")
    void updateEffective();

    /**
     * 定时查询状态为“公示”的公示结束时间，过期将状态改为生效
     */
    @Update("UPDATE score_detail SET state=7 WHERE state=2 AND publicity_end_time<NOW()")
    void updatePublicityTime();

    /**
     * 查询企业信用得分记录列表
     *
     * @param year 年度
     * @return 列表
     */
    @Select("SELECT a.id, a.poguid, IFNULL(a.jc_01,0) as jc01, IFNULL(a.jc_02,0) as jc02,IFNULL(a.jc_03,0) as jc03,IFNULL(a.jc_04 ,0) as jc04, IFNULL(a.jc_05,0) as jc05, IFNULL(a.jc_06,0) as jc06, IFNULL(a.jc_07,0) as jc07," +
            " IFNULL(b.yl_01,0) as yl01, IFNULL(b.yl_02,0) as yl02,IFNULL(b.yl_03 ,0) as yl03, IFNULL(b.yl_04,0) as yl04, IFNULL(b.yl_05,0) as yl05, IFNULL(b.yl_06,0) as yl06, IFNULL(b.yl_07,0) as yl07, IFNULL(b.yl_08,0) as yl08," +
            " IFNULL(b.fm_01,0) as fm01, IFNULL(b.fm_02,0) as fm02, IFNULL(b.fm_03,0) as fm03, IFNULL(b.fm_04,0) as fm04, IFNULL(b.fm_05,0) as fm05,IFNULL(b.fm_06 ,0) as fm06, IFNULL(b.fm_07,0) as fm07 FROM score_record a " +
            "LEFT JOIN (SELECT corp_id," +
            "SUM( CASE WHEN score_type_id = 1 THEN score ELSE 0 END) as yl_01," +
            "SUM( CASE WHEN score_type_id = 2 THEN score ELSE 0 END) as yl_02," +
            "SUM( CASE WHEN score_type_id = 3 THEN score ELSE 0 END) as yl_03," +
            "SUM( CASE WHEN score_type_id = 4 THEN score ELSE 0 END) as yl_04," +
            "SUM( CASE WHEN score_type_id = 5 THEN score ELSE 0 END) as yl_05," +
            "SUM( CASE WHEN score_type_id = 6 THEN score ELSE 0 END) as yl_06," +
            "SUM( CASE WHEN score_type_id = 7 THEN score ELSE 0 END) as yl_07," +
            "SUM( CASE WHEN score_type_id = 8 THEN score ELSE 0 END) as yl_08," +
            "SUM( CASE WHEN score_type_id = 9 THEN score ELSE 0 END) as fm_01," +
            "SUM( CASE WHEN score_type_id = 10 THEN score ELSE 0 END) as fm_02," +
            "SUM( CASE WHEN score_type_id = 11 THEN score ELSE 0 END) as fm_03," +
            "SUM( CASE WHEN score_type_id = 12 THEN score ELSE 0 END) as fm_04," +
            "SUM( CASE WHEN score_type_id = 13 THEN score ELSE 0 END) as fm_05," +
            "SUM( CASE WHEN score_type_id = 14 THEN score ELSE 0 END) as fm_06," +
            "SUM( CASE WHEN score_type_id = 15 THEN score ELSE 0 END) as fm_07" +
            " FROM score_detail WHERE  state IN (2, 4, 5, 6, 7) AND publicity_begin_time <= NOW() AND effective_end_time >= NOW() GROUP BY corp_id) b ON a.poguid =b.corp_id  WHERE a.`year`=#{year}")
    List<ScoreRecord> getScoreRecordList(@Param("year") String year);

    /**
     * 每年更新一次信用等级得分
     *
     * @param scoreRecord 信用等级得分
     * @return 结果
     */
    @Update("update score_record set jc_01=#{r.jc01},jc_02=#{r.jc02},jc_03=#{r.jc03}," +
            "jc_04=#{r.jc04},jc_05=#{r.jc05},jc_06=#{r.jc06},jc_07=#{r.jc07}," +
            "yl_01=#{r.yl01},yl_02=#{r.yl02},yl_03=#{r.yl03},yl_04=#{r.yl04}," +
            "yl_05=#{r.yl05},yl_06=#{r.yl06},yl_07=#{r.yl07},yl_08=#{r.yl08}," +
            "fm_01=#{r.fm01},fm_02=#{r.fm02},fm_03=#{r.fm03},fm_04=#{r.fm04}," +
            "fm_05=#{r.fm05},fm_06=#{r.fm06},fm_07=#{r.fm07},all_score=#{r.allScore}," +
            "dj=#{r.dj},gmt_modified=now() where id=#{r.id}")
    int updateScoreRecord(@Param("r") ScoreRecord scoreRecord);

    /**
     * 新增当年得分等级
     *
     * @param scoreRecord 信用等级实体
     * @return 结果
     */
    @Insert("INSERT INTO score_record(jc_01,jc_02,jc_03,jc_04,jc_05,jc_06,jc_07,yl_01,yl_02,yl_03,yl_04,yl_05,yl_06,yl_07,yl_08," +
            "fm_01,fm_02,fm_03,fm_04,fm_05,fm_06,fm_07,all_score,poguid,gmt_create,gmt_modified,dj,year) " +
            "VALUES(#{sr.jc01},#{sr.jc02},#{sr.jc03},#{sr.jc04},#{sr.jc05},#{sr.jc06},#{sr.jc07},#{sr.yl01},#{sr.yl02},#{sr.yl03},#{sr.yl04},#{sr.yl05},#{sr.yl06},#{sr.yl07},#{sr.yl08}," +
            "#{sr.fm01},#{sr.fm02},#{sr.fm03},#{sr.fm04},#{sr.fm05},#{sr.fm06},#{sr.fm07},#{sr.allScore},#{sr.poguid},now(),now(),#{sr.dj},#{sr.year})")
    int addScoreRecord(@Param("sr") ScoreRecord scoreRecord);

    /**
     * 增加一条定时任务时间
     *
     * @param time 设定的时间
     * @param name 添加人
     * @return 结果
     */
    @Insert("INSERT INTO scheduled_time (gmt_create,gmt_modified,time,adder) VALUES(NOW(),NOW(),#{time},#{name})")
    int addTime(@Param("time") String time, @Param("name") String name);

    /**
     * 获取最近一次设定的时间
     *
     * @return 时间
     */
    @Select("SELECT time FROM scheduled_time ORDER BY gmt_create DESC limit 1")
    String getTime();

}
